﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data;
using System.IO;
using System.Data.SqlClient;
using System.Text.RegularExpressions;


namespace WebServices.Modeling.Database
{
    class DatabaseConnecter
    {
        #region Vars
        private string _s_dbName;
        private string _s_dbHost;
        private string _s_dbUser;
        private string _s_dbPass;

        private SqlConnection _ob_SQLConnecter;
        #endregion


        private bool getConfigInfo ()
        {
            string sFilePath = "..\\config\\config.txt";

            // Get info from config file
            // Please take note this file is a clear text file, please check this part later to improve security

            string[] bufferArr = { null, null, null, null, null };
            string[] strContent;

            int index = 0;

            char spliter = '='; // We use "=" as spliter

            StreamReader fileObj = File.OpenText(sFilePath);

            while ((bufferArr[index] = fileObj.ReadLine()) != null) //
            {
                strContent = bufferArr[index].Split(spliter);
                bufferArr[index] = strContent[1];
                index++;
            }
            fileObj.Close();

            // Assign values which got from config.txt to class properties, we can use those info later 
            _s_dbHost = bufferArr[0];
            _s_dbName = bufferArr[1];
            _s_dbUser = bufferArr[2];
            _s_dbPass = bufferArr[3];
            

            // Try to connect SQL server here, if fail, return false
            bool isConnected = false;
            isConnected = this.ConnectSQLServer();

            return isConnected;
        }


        private bool ConnectSQLServer()
        {
            try
            {
                //Connect data base config info
                //Consider parameter which paste into connect query
                string dbInfo = "server =" + _s_dbHost + ";" +
                    "database=" + _s_dbName + ";" +
                    "uid=" + _s_dbUser + ";" +
                    "pwd=" + _s_dbPass + ";" + "connection Timeout = 0;" + "max pool size = 100000;";

                // Make the connect 
                _ob_SQLConnecter = new SqlConnection(dbInfo);


                // Open the connection
                _ob_SQLConnecter.Open();

                // Connect success
                return true;
            }
            catch (SqlException SQLEx)
            {
                Console.WriteLine("Cannot connect to SQL server. Error:" + SQLEx.Message);
                return false;
            }

        }

        public void Open()
        {
            // Open connection
            _ob_SQLConnecter.Open();
        }

        public void Close()
        {
            // Close connection
            _ob_SQLConnecter.Close();
        }

        public void disconnectSQLServer()
        {
            //Disconnect SQL server
            _ob_SQLConnecter.Close();
        }

        // Excute an SQL query and return an SQLDataReader, using for SELECT or etc query
        public SqlDataReader ExcuteSQLQuery(string query)
        {
            try
            {
                ConnectSQLServer();
                SqlCommand dbCommand = _ob_SQLConnecter.CreateCommand();
                dbCommand.CommandText = (string)query.Clone();
                SqlDataReader dbReader = dbCommand.ExecuteReader();
                return dbReader;
            }
            catch (SqlException ex)
            {
                Console.WriteLine("Exception" + ex.Message);
            }
            return null;
        }



        public void ExcuteNonSQL(string query)
        {
            try
            {
                // Check the sate of the sql connection and re-open if the connection has been closed
                if (_ob_SQLConnecter.State == ConnectionState.Closed)
                {
                    _ob_SQLConnecter.Open();
                }
                SqlCommand dbCommand = _ob_SQLConnecter.CreateCommand();
                dbCommand.CommandText = query;
                dbCommand.ExecuteNonQuery();

                //Close sql connection
                _ob_SQLConnecter.Close();
            }
            catch (SqlException SQLex)
            {
                Console.WriteLine("This command return an error, " + SQLex.Message);

            }

        }
    }
}